/****** Object:  Stored Procedure dbo.CGO_TriviaRegenerate    Script Date: Monday, March 07, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.CGO_TriviaRegenerate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.CGO_TriviaRegenerate
GO

/* -------------------------------------------------------------------------------------
/   AccountGet
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.CGO_TriviaRegenerate	
  @EnrollID INT,
  @UserID INT
AS
  IF NOT EXISTS ( SELECT  1
                  FROM    dbo.CGO_GameEnrollment M
                  INNER JOIN dbo.CGO_GameEnrollmentStatus S ON M.EnrollStatusID=S.StatusID
                  WHERE   EnrollmentID = @EnrollID
                          AND UserID = @UserID 
                          AND StatusCode='PLAYING') 
  BEGIN
  	RETURN
  END                          
    
  DECLARE @QuestionIndex INT  
  DECLARE @QuestionID INT
  DECLARE @CategoryID INT
  
  SELECT  @QuestionIndex = MIN(QuestionIndex)
  FROM    dbo.CGO_TriviaResult
  WHERE   GameEnrollmentID = @EnrollID
          AND ResultStatusID = 1
          AND QuestionIndex > 0
  
  SELECT  @QuestionID = QuestionID
  FROM    dbo.CGO_TriviaResult
  WHERE   GameEnrollmentID = @EnrollID
          AND QuestionIndex = @QuestionIndex
          
  SELECT  @CategoryID = CategoryID
  FROM    dbo.CGO_TriviaQuestion
  WHERE   QuestionID = @QuestionID
  
  IF EXISTS ( SELECT  1
              FROM    dbo.CGO_TriviaQuestion Q
              WHERE   Q.CategoryID = @CategoryID
                      AND NOT EXISTS ( SELECT 1
                                       FROM   dbo.CGO_TriviaResult M
                                              INNER JOIN dbo.CGO_GameEnrollment G ON M.GameEnrollmentID = G.EnrollmentID
                                              INNER JOIN dbo.CGO_GameEnrollmentStatus S ON G.EnrollStatusID = S.StatusID
                                       WHERE  G.UserID = @UserID
                                              AND S.StatusCode IN ( 'PLAYING','COMPLETED' )
                                              AND M.QuestionID = Q.QuestionID )) 
  BEGIN
  
    UPDATE dbo.CGO_TriviaResult
    SET    QuestionIndex = 0 ,
           ResultStatusID = 3,
           LastUpdateDate = GETDATE()
    WHERE  GameEnrollmentID = @EnrollID
          AND QuestionIndex = @QuestionIndex
	  
	  INSERT INTO dbo.CGO_TriviaResult
	          ( GameEnrollmentID ,
	            QuestionIndex ,
	            QuestionID,
	            ResultStatusID
	          )
   SELECT TOP 1
          @EnrollID ,
          @QuestionIndex ,
          QuestionID ,
          1
   FROM   dbo.CGO_TriviaQuestion Q
   WHERE  Q.CategoryID = @CategoryID
          AND NOT EXISTS ( SELECT 1
                           FROM   dbo.CGO_TriviaResult M
                                  INNER JOIN dbo.CGO_GameEnrollment G ON M.GameEnrollmentID = G.EnrollmentID
                                  INNER JOIN dbo.CGO_GameEnrollmentStatus S ON G.EnrollStatusID = S.StatusID
                           WHERE  G.UserID = @UserID
                                  AND s.StatusCode IN ( 'PLAYING', 'COMPLETED' )
                                  AND M.QuestionID = Q.QuestionID )
    ORDER BY NEWID()
  END
  
  SELECT  COUNT(*)
  FROM    dbo.CGO_TriviaResult
  WHERE   GameEnrollmentID = @EnrollID
          AND QuestionIndex > 0
          AND ResultStatusID = 1
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO